"""
操作cgtw数据库
"""
from .CONFIG import CGTW
import time
import psycopg2
from .mz_date import MZ_Date

# DATABASE= 'cgtw'
# USER = 'timeaxis'
# PASSWORD = 'timeaxis'
# HOST = '192.168.1.231'
# PORT= 5432
DATABASE = CGTW['database']
USER = CGTW['user']
PASSWORD = CGTW['password']
HOST = CGTW['host']
PORT= CGTW['port']





class CGTW6Handler(object):
    """
    这里操作cgtw 6版本
    """
    def __init__(self,_CGTW=None):
        super(CGTW6Handler, self).__init__()


        self.real_cgtw_host = _CGTW['host'] if _CGTW else CGTW['host']
        self.real_cgtw_port = _CGTW['port'] if _CGTW else CGTW['port']

        self.mz_date=MZ_Date()

        #init data 以下都是创建handler之后一次性执行存入到内存里
        self.department_id_info,self.id_department_info = self._get_department_info()
        self.pipeline_id_info, self.id_pipeline_info = self._get_pipeline_info()
        self.database_project_info,self.active_database_project_info = self._get_project_info()

        self.account_info,self.department_id_account_info, self.account_id_department_map = self.get_cgtw_account_info()


    def _init_cgtw_db(self):
        """
        初始化231 cgtw数据库连接
        :return: time_connect, time_cursor
        """
        try:
            cg_connect = psycopg2.connect(database=DATABASE,
                                            user=USER,
                                            password=PASSWORD,
                                            host=self.real_cgtw_host,
                                            port=self.real_cgtw_port)
            cg_cursor = cg_connect.cursor()
        except Exception as cgdb_err:
            print('cgdb_err:', cgdb_err)
            return None, None
        return cg_connect, cg_cursor

    """
    ------------------------------------------------------------------------------------------------------------------
    schema: public 
    ------------------------------------------------------------------------------------------------------------------
    """


    """
    ***********************************************
        config表操作
        内容:记录部门信息，阶段名称以及权限信息
        字段:
        id: 
        object: 有3种，department部门信息 , pipeline阶段信息 , permission_group权限组
        json_data:对应object的具体信息

    ***********************************************
    """
    def _get_manager_info(self):
        """
        获取管理者账号信息
        :return:
        """
        cg_connect, cg_cursor = self._init_cgtw_db()
        sql = "select id,json_data->'name' from entity where module ='account' and json_data ->>'manager' ='Y' "
        mannager_info = {}
        try:
            cg_cursor.execute(sql)
            result = cg_cursor.fetchall()
            if result:
                for i in result:
                    aid,name = i
                    mannager_info[aid] = name

        except Exception as _get_manager_info_ERR:
            print('_get_manager_info_ERR:', str(_get_manager_info_ERR))

        finally:
            cg_connect.close()

        return mannager_info

    def _get_department_info(self):
        """
        获取部门信息，获取的内容也就是department_id对应的部门中文名称
        :return:
            部门id表department_id_info：{‘01美术’:xxxxx,'12TD':xxx,...}
            id部门表id_department_info：{xxxxxid:'01美术',xxx:'12TD',...}
        """
        department_id_info={}
        id_department_info={}
        cg_connect, cg_cursor = self._init_cgtw_db()
        sql ="select id,json_data from config where object ='department' "

        try:
            cg_cursor.execute(sql)
            result=cg_cursor.fetchall()
            if result:
                for i in result:
                    department_id=i[0]
                    data=i[1]
                    department=data['entity']
                    department_id_info[department]=department_id
                    id_department_info[department_id]=department
        except Exception as get_department_info_ERR:
            print('get_department_info_ERR:',str(get_department_info_ERR))

        finally:
            cg_connect.close()

        return department_id_info,id_department_info

    def _get_pipeline_info(self):
        """
        获取阶段名称,初始化时就从public表中把所有pipeline_id与阶段名称拿到手
        :return: 阶段id表：{'mg04',pipeline_id,'cpt_s_hero',pipeline_id,..}
                 id阶段表: {pipeline_id:'mg04',pipeline_id:'cpt_s_hero',...}
        """
        pipeline_id_info = {}
        id_pipeline_info = {}
        cg_connect, cg_cursor = self._init_cgtw_db()
        sql = "select id,json_data from config where object ='pipeline' "

        try:
            cg_cursor.execute(sql)
            result = cg_cursor.fetchall()
            if result:
                for i in result:
                    pipeline_id = i[0]
                    data = i[1]
                    pipeline = data['entity']
                    pipeline_id_info[pipeline] = pipeline_id
                    id_pipeline_info[pipeline_id] = pipeline

        except Exception as get_pipeline_info_ERR:
            print('get_pipeline_info_ERR:', str(get_pipeline_info_ERR))

        finally:
            cg_connect.close()

        return pipeline_id_info,id_pipeline_info




    """
    ***********************************************
        entity表操作
        内容:项目信息，账户信息
        字段:
        id: 
        module：模块名 有project,account
        module_type：模块归属类型
        link_id:暂无
        json_data:对应object的具体信息

    ***********************************************
    """

    def _get_active_project(self):
        """
        获取激活状态的项目,包含database与full_name
        :return: data = {'database':full_name,'database':'full_name',...,}

        """
        data = {}
        sql = "select json_data ->'database',json_data ->'full_name'  from public.entity where json_data->>'status' = 'Active'"
        cg_connect, cg_cursor = self._init_cgtw_db()
        try:
            cg_cursor.execute(sql)
            result = cg_cursor.fetchall()
            if result:
                for i in result:
                    database,full_name = i
                    data[database] = full_name
        except Exception as _get_active_project_ERR:
            print('_get_active_project_ERR:',_get_active_project_ERR)
        finally:
            cg_connect.close()

        return data


    def _get_project_info(self):
        """
        获取项目信息

        除了直接获取cgtw public中的项目信息，还需要从每个项目schema中enity表中获取该项目的所以集数信息
        :return: database项目表:{'proj_by':data,'proj_lhy':data,...}
                其中data:
                    data['cg_id'] = pid
                    data['database']=database
                    data['entity_name'] = entity_name
                    data['full_name']=full_name
                    data['color']=color
                    data['status']=status
                    data['resolution']=resolution
                    data['frame_rate']=frame_rate
                    data['project_name']=project_name
                    data['path']=path
                    data['eps']=eps  #这个需要去对应的项目schema中的entity中统计获取
                    data['project_material_path']=project_material_path  #这个需要去对应的项目schema中的entity中统计获取
                    data['create_time'] = create_time  项目创建时间
        """
        database_project_info={}
        active_database_project_info = {}  #激活的项目信息
        cg_connect, cg_cursor = self._init_cgtw_db()
        sql ="select id,json_data from entity where module ='project' "

        try:
            cg_cursor.execute(sql)
            result = cg_cursor.fetchall()
            if result:
                for i in result:
                    pid, json_data = i
                    color = json_data.get('color', '')
                    entity_name = json_data.get('entity', '')
                    status = json_data.get('status', '')
                    database = json_data.get('database', '')
                    full_name = json_data.get('full_name', '')
                    resolution = json_data.get('resolution', '')
                    frame_rate = json_data.get('frame_rate', '')
                    path = json_data.get('path', '')
                    project_material_path = json_data.get('project_material_path', '')
                    create_time = json_data.get('create_time','')
                    project_name = '{}({})'.format(full_name, entity_name)

                    data = {}
                    data['cg_id'] = pid
                    data['database'] = database
                    data['entity_name'] = entity_name
                    data['full_name'] = full_name
                    data['color'] = color
                    data['status'] = status
                    data['resolution'] = resolution
                    data['frame_rate'] = frame_rate
                    data['project_name'] = project_name
                    data['path'] = path
                    data['eps'] = ''
                    data['project_material_path'] = project_material_path
                    data['create_time'] = create_time
                    database_project_info[database] = data
                    if status == 'Active':
                        eps_sql = f"select json_data->'entity' from {database}.entity where module = 'eps'"
                        cg_cursor.execute(eps_sql)
                        eps_result = cg_cursor.fetchall()
                        eps_list = []
                        if eps_result:
                            for t in eps_result:
                                eps_list.append(t[0])
                        eps = '|'.join(eps_list)
                        data['eps'] = eps
                        active_database_project_info[database] = data

        except Exception as get_project_info_ERR:
            print('get_project_info_ERR:',str(get_project_info_ERR))

        finally:
            cg_connect.close()

        return database_project_info,active_database_project_info


    def get_cgtw_account_info(self):
        """
        获取人员账户信息
        通过account_id可以获取对应的department_id ，account_id在public的entity表中的，也是其id (entity)
        :return:
            1.返回以部门id为键的人员信息列表，department_id_account_info：{'department_id1':[data1,data2,...],
                                                                     'department_id2':[data1,data2,..],
                                                                     ...}
               data: {'account_id':xxx,'artist':xxx,'attribute':xxx,'status':xx,
                      'level':xx,'department_id':xx,'department':xxx}
            2. account_id_department_map {account_id1: '07特效一',account_id2:xxx,...}
        """
        account_info = {}
        department_id_account_info={}
        account_id_department_map = {} #{'account_id': 部门}
        cg_connect, cg_cursor = self._init_cgtw_db()
        sql ="select id,json_data,department_id from entity where module ='account' "

        try:
            cg_cursor.execute(sql)
            result=cg_cursor.fetchall()
            if result:
                for i in result:
                    account_id, json_data, department_id = i
                    artist = json_data['name']
                    attribute = json_data.get('attribute', '非研发人员')
                    status = json_data['status']
                    level = json_data.get('level', 'P0')

                    data = {}
                    data['cg_id'] = account_id
                    data['account_id'] = account_id
                    data['artist'] = artist
                    data['attribute'] = attribute
                    data['status'] = status
                    data['level'] = level
                    data['department_id'] = department_id
                    data['active_department'] = department_id
                    data['department'] = self.id_department_info.get(department_id, '')
                    data['active_department'] = json_data.get('active_department',
                                                              data['department'])  # 没有active_department则为此时的department

                    account_info[account_id] = data

                    if department_id not in department_id_account_info.keys():
                        department_id_account_info[department_id]=[data]
                    else:
                        department_id_account_info[department_id].append(data)



                    account_id_department_map[account_id] = data['department']

        except Exception as get_account_info_ERR:
            print('get_account_info_ERR:',str(get_account_info_ERR))
        finally:
            cg_connect.close()

        return account_info,department_id_account_info,account_id_department_map

    """
    ------------------------------------------------------------------------------------------------------------------
    schema: proj_ 
    ------------------------------------------------------------------------------------------------------------------
    """



    """
    ***********************************************
        entity表操作
       
        字段:
        id: 
        module: 模块类型，shot,eps,asset,asset_type
        module_type: task info
        link_id: 项目拼音简称 eg : XXSS
        flow_id:   项目中文名称 eg: 西夏死书
        json_data:   cgtw中项目的颜色
        pipeline_id: 项目状态  eg: Active,Close
        eps_id:  项目中含有的集数 eg: {"eps_list":["01","02",...]}


    ***********************************************
    """
    
    
    def date2stamp(self,date_formate):
        """
        将日期转成时间戳
        :param date_formate:
        :return:
        """

        s = time.strptime(date_formate, '%Y-%m-%d %H:%M:%S')
        ts = int(time.mktime(s))
        return ts

    def convertTimeToWorkhour(self, time_str):
        """
        将时间格式转成小时，6版的返修时间已经变成秒了
        :param time_str:
        :return:
        """
        # str_hour, str_minute = time_str.split(':')
        # hour = float(str_hour)
        # minute = float(str_minute)
        # all_hour = hour + round(minute * 1.0 / 60, 2)

        all_hour = float(time_str)/3600.0
        return all_hour


    def get_current_workhour(self,ori_workhour,retake_counts,client_retake_info):
        """
        通过返修次数与返修信息来对比
        
        :param retake_counts: 
        :param client_retake_info: 
        :return: 
        """
        if client_retake_info and client_retake_info!='[]':
            retake_workhour_list = []
            client_retake_info = eval(client_retake_info)
            for i in client_retake_info:
                retake_workhour = str(i.get('workingHours', "0"))
                retake_workhour_list.append(retake_workhour)
            if retake_counts == len(client_retake_info):
                last_workhour = client_retake_info[0].get('workingHours', "0")
            else:
                last_workhour = "0"
            str_retake_workhour = ','.join(retake_workhour_list)
        else:
            str_retake_workhour = ''
            last_workhour = ori_workhour
        return str_retake_workhour,last_workhour



    def getTimeLogInfoByTaskId(self,cg_cursor,schema,task_id,retake_counts):
        """
        获取该任务最新TimeLog信息中的工时记录列表，当没有时，排期软件发现不匹配默认为0工时
        最新工时既然在数据库里了，那么就继续拿吧
        """
        sql = "select json_data from time_log where db = '{}' and link_id ='{}'".format(schema, task_id)
        cg_cursor.execute(sql)
        result = cg_cursor.fetchall()
        last_workhour ='-1'#当没有timelog时最新工时为-1，用于排除，不需要排除了就是0
        retake_workhour='0'#2,1.3,0.5
        if result:
            create_time_workhour_map = {}
            for i in result:
                create_time = i[0]['create_time']
                stamp_time = self.date2stamp(create_time)
                use_time = i[0]['use_time']
                hour = str(self.convertTimeToWorkhour(use_time))
                # retake_workhour_list.append(hour)
                create_time_workhour_map[stamp_time] = hour
            ka = create_time_workhour_map.keys()
            ska = sorted(ka)
            retake_workhour_list = [create_time_workhour_map[i] for i in ska]
            r_count=len(retake_workhour_list)
            if(r_count)!=retake_counts:  #当返修总次数与time log数不一致时
                deltel_count=retake_counts-r_count#1.当返修次数大于timelog数量时,在timelog列表前面补'0'
                if deltel_count>0:
                    for i in range(deltel_count):
                        retake_workhour_list.insert(0,'0')
                elif deltel_count<0:#2.当返修次数小于timelog时，多出来的timelog就不要
                    retake_workhour_list=retake_workhour_list[:deltel_count]

            retake_workhour = ','.join(retake_workhour_list)
            last_workhour = retake_workhour_list[-1] if retake_workhour_list else '-1'
        return retake_workhour, last_workhour

    def clear_workhour(self,workhour):
        """
        工时转化为标准的可转为
        :param workhour:
        :return:
        """
        if self.is_number(workhour):
            return workhour
        else:
            return '0'

    def get_cgtw_task_info(self,schema,task_id_list=None,id_pipeline_map=None):
        """
        在entity表中获取任务信息。 仅获取有人名的任务。
        任务分为asset任务与shot任务,每个任务的部分信息又在对应模块的info里
        moduletask
        :param schema: 项目database
        :param task_id_list: 传入需要获取的任务id列表，该任务id就是endity表中的id,可用于指向查询所需要的任务信息
        :param id_pipeline_map: 阶段的id与阶段信息表
        :return:
        """

        # current_task = None

        def _get_current_workhour(ori_workhour, retake_counts, client_retake_info):
            """
            通过返修次数与返修信息来对比

            :param retake_counts:
            :param client_retake_info:
            :return:
            """
            if client_retake_info and client_retake_info != '[]':
                retake_workhour_list = []
                client_retake_info = eval(client_retake_info)
                for i in client_retake_info:
                    retake_workhour = str(i.get('workingHours', "0"))
                    retake_workhour_list.append(retake_workhour)
                if retake_counts == len(client_retake_info):
                    last_workhour = client_retake_info[0].get('workingHours', "0")
                else:
                    last_workhour = "0"
                str_retake_workhour = ','.join(retake_workhour_list)
            else:
                str_retake_workhour = ''
                last_workhour = ori_workhour
            return str_retake_workhour, last_workhour


        def _get_task_data(schema,tid, module, link_id, pipeline_id, json_data,id_pipeline_map,account_id_department_map):
            data = {}
            data['id'] = tid  # entity表中id
            data['cg_id'] = schema + ',' + module + ',' + tid
            data['artist'] = json_data.get('artist', '')
            if data['artist'] and ',' not in data['artist']:  # 仅分配了制作者的cgtw才获取,且是单人任务
                data['module'] = module
                data['task_name'] = json_data.get('entity', '')
                data['status'] = json_data.get('status', '')
                director_status_retake_count = int(json_data.get('director_status_retake_count', '0'))
                client_status_retake_count = int(json_data.get('client_status_retake_count', '0'))
                total_count = director_status_retake_count + client_status_retake_count
                data['client_status_retake_count'] = total_count
                data['assigned'] = 0  # 这个需要在同步时更新掉
                data['database'] = schema
                data['difficulty_level'] = json_data.get('difficulty_level', '')
                data['important_task_mark'] = json_data.get('important_task_mark', '')
                data['workhour'] = json_data.get('work_hour', '0')
                data['account_id'] = json_data.get('account_id', '')
                data['department'] = account_id_department_map.get(data['account_id'], '')
                data['client_retake_info'] = json_data.get('client_retake_workhour',
                                                           None)  # 这是一个str  然后 cgtw字段名称该了，我这边key不需要动
                data['retake_workhour'], data['last_workhour'] = _get_current_workhour(data['workhour'],
                                                                                           total_count, data[
                                                                                               'client_retake_info'])
                data['last_submit_time'] = json_data.get('last_submit_time', '')
                data['task_priority'] = json_data.get('task_priority', '')
                data['task_pipeline'] = id_pipeline_map.get(pipeline_id, '')  # 获取阶段名称
                data['account_id'] = json_data.get('account_id', '')
                image = json_data.get('image', [])  # 缩略图到 task data里了 不在info里了
                try:
                    data['img_path'] = image[0].get('min', '') if image else ''  # 妈的 这个img_path杂七杂八，各种标识
                except Exception as img_path_ERR:
                    print('img_path_ERR:',str(img_path_ERR))
                """以下获取阶段信息，即asset/shot信息,包括：
                    description : 内容描述
                    name ： asset/shot 名称， asset需要中文
                    eps ： 所在集数
                    #img_path ：缩略图路径
                    frame ： 帧数
                    assign_pipeline ：相关阶段
                    shot_difficulty_level : 镜头难度,仅shot类型
                    deadline : 日期节点,仅shot类型
                """
                info_sql = "select link_id,json_data from {}.entity where id ='{}'".format(schema,
                                                                                           link_id)  # 获取对应的asset/shot信息
                cg_cursor.execute(info_sql)
                info_result = cg_cursor.fetchone()
                data['description'] = ''  # 占位
                data['name'] = ''  # 占位
                data['eps'] = ''  # 占位
                data['pinyin'] = ''  # 占位
                data['frame'] = '0'  # 占位
                data['assign_pipeline'] = ''  # 占位
                data['shot_difficulty_level'] = ''  # 占位
                data['deadline'] = ''  # 占位
                data['show_name'] = ''  # 占位

                if info_result:
                    info_link_id, info_json_data = info_result

                    vfx_note = info_json_data.get('vfx_note', '')
                    try:
                        data['description'] = info_json_data.get('description', '') if not vfx_note else vfx_note
                        data['description'] = data['description'].replace("'", '')
                    except Exception as description_ERR:
                        print('description_ERR:',str(description_ERR))
                    data['eps'] = ''
                    data['pinyin'] = info_json_data['entity']
                    image_list = info_json_data.get('image', None)
                    if image_list:
                        try:
                            min_image = image_list[0].get('image', None) if image_list else None
                            min_image = image_list[0].get('min', None) if not min_image else min_image
                            data['img_path'] = min_image if min_image else data['img_path']
                        except Exception as min_image_ERR:
                            print('min_image_ERR:',str(min_image_ERR))
                    if module == 'asset':  # 资产的话，则取中文名
                        data['name'] = info_json_data.get('cn_name', '')
                    elif module == 'shot':  # 镜头的话，则取镜头名称
                        data['name'] = info_json_data['entity']
                        eps_sql = "select module,link_id,json_data from {}.entity where id ='{}'".format(schema, info_link_id)
                        cg_cursor.execute(eps_sql)
                        eps_result = cg_cursor.fetchone()
                        if eps_result:
                            emodule,elink_id,ejson_data = eps_result
                            if emodule == 'eps':
                                data['eps'] = ejson_data.get('entity', '')
                            elif emodule == 'seq':
                                eps_sql2 = "select json_data from {}.entity where id ='{}'".format(schema,
                                                                                                        elink_id)
                                cg_cursor.execute(eps_sql2)
                                eps_result2 = cg_cursor.fetchone()
                                if eps_result2:
                                    data['eps'] = eps_result2[0].get('entity', '')

                    data['frame'] = info_json_data.get('frame', '0')
                    data['assign_pipeline'] = info_json_data.get('assign_pipeline', '')
                    data['shot_difficulty_level'] = info_json_data.get('shot_difficulty_level', '')
                    data['deadline'] = info_json_data.get('deadline', '')

                    # 拼接成show_name
                    data['show_name'] = '-'.join([data['name'], data['task_name']])

                return data

            else:
                return None

        cgtw_tasks = []
        id_pipeline_map = id_pipeline_map if id_pipeline_map else self.id_pipeline_info
        cg_connect, cg_cursor = self._init_cgtw_db()

        if task_id_list:
            # connect_ids = '|'.join(task_id_list)  # 拼接不行，超出similar 长度
            # task_sql = "select id,module,link_id,pipeline_id,json_data from {}.entity where module_type='task' and id similar to '({})' ".format(
            #     schema,connect_ids)
            try:
                for i in task_id_list:
                    task_sql = f"select id,module,link_id,pipeline_id,json_data from {schema}.entity where module_type='task' and id = '{i}'"
                    cg_cursor.execute(task_sql)
                    result = cg_cursor.fetchone()
                    if result:
                        tid, module, link_id, pipeline_id, json_data = result
                        data = _get_task_data(schema,tid, module, link_id, pipeline_id, json_data,id_pipeline_map,self.account_id_department_map)
                        if data:
                            cgtw_tasks.append(data)
            except Exception as get_cgtw_task_by_task_id_list_ERR:
                print('get_cgtw_task_by_task_id_list_ERR:',get_cgtw_task_by_task_id_list_ERR)

        else:
            task_sql = "select id,module,link_id,pipeline_id,json_data from {}.entity where module_type='task'".format(
                schema)
            try:
                cg_cursor.execute(task_sql)
                result = cg_cursor.fetchall()
                if result:
                    for i in result:  #这里是task任务信息
                        tid, module, link_id, pipeline_id, json_data = i
                        data = _get_task_data(schema, tid, module, link_id, pipeline_id, json_data, id_pipeline_map,
                                              self.account_id_department_map)
                        if data:
                            cgtw_tasks.append(data)

            except Exception as get_cgtw_task_by_schema_ERR:
                print('get_cgtw_task_by_schema_ERR:',get_cgtw_task_by_schema_ERR)

        cg_connect.close()

        return cgtw_tasks





    """
    ***********************************************
    ***********************************************

    测试

    ***********************************************
    ***********************************************
    """




    def _test_get_column_types(self,schema_table_name,column_name):
        """
        获取module中指定的列中的所有类型
        :param schema_table_name:  eg:proj_by.entity
        :param column_name:
        :return:
        """
        cg_connect, cg_cursor =self._init_cgtw_db()
        try:
            sql="select {} from {} ".format(column_name,schema_table_name)
            cg_cursor.execute(sql)
            result= cg_cursor.fetchall()
            if result:
                module_set=set(result)
                print(module_set)
                print(len(module_set))
        except Exception as _test_get_column_types_ERR:
            print('_test_get_column_types_ERR:',str(_test_get_column_types_ERR))

        finally:
            cg_connect.close()